package utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;
import java.util.Properties;

public final class JDBCHelper {
    public static void main(String[] args) {
        System.out.println(database );
        System.out.println(driverClassName );
        System.out.println(username );
        System.out.println(password );
        System.out.println(url );
        System.out.println(autocommit );
        System.out.println(isolation );
    }
    /**表示连接哪个数据库 */
    private static String database ;
    /** 驱动 */
    private static String driverClassName ;
    /**用户名 */
    private static String username ;
    /**密码*/
    private static String password ;
    /**协议 */
    private static String url ;
    /** 是否是自动提交事务 */
    private static boolean autocommit ;
    /** 设置事务的隔离级别 */
    private static int isolation ;
    /**连接*/
    private static Connection conn ;
    /**
     * 期望不能被实例化
     */
    private JDBCHelper() {}
    /**
     * 使用静态代码块来加载配置文件中的信息
     */
    static { config() ; }
    /**
     * 读取配置文件
     */
    private static void config() {
        Properties p = new Properties() ;
        InputStream ins = JDBCHelper.class.getResourceAsStream("/db.properties") ;
        try {
            p.load( ins );
            database = p.getProperty("connect") ;
            driverClassName = p.getProperty(database + ".driver") ;
            username = p.getProperty(database + ".username") ;
            password = p.getProperty(database + ".password") ;
            url = p.getProperty(database + ".url") ;
            String autoCommit = p.getProperty("autocommit") ;
            autocommit = Boolean.valueOf(autoCommit) ;
            String tx_isolation = p.getProperty("transactionIsolation") ;
            isolation = Integer.valueOf(tx_isolation) ;

            if(isolation <= 0 || (isolation != 1 || isolation !=2 || isolation != 4 || isolation != 8)) {
                isolation = 2;
            }
        } catch (IOException e) {
            System.out.println("读取配置文件失败" + e.getMessage() );
        }
    }

    /**
     * 加载驱动
     */
    private static void load() {
        try {
            Class.forName(driverClassName) ;
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败 ：" + e.getMessage() );
        }
    }

    /**
     * 验证Connection是否是有效的，且没有关闭
     * @return true 表示连接有效
     * @return false 表示连接无效
     */
    private static boolean isValidate() {
        if( Objects.nonNull(conn) ) {
            // 判断没有关闭，是有效的
            try {
                /**
                 * isValid  如果是没有关闭的且  有效的，返回 true
                 */
                System.out.println("连接有效——valid");
                return conn.isValid(10) ;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 建立连接
     */
    public static void connect(){
        load() ;
        try {
            if( !isValidate() ) { // 判断conn如果是空，或是无效的，则创建Connection对象
                conn = DriverManager.getConnection(url , username , password ) ;
                System.out.println("连接数据库成功-connect");
            }
        } catch (SQLException e) {
            System.out.println("创建连接失败 ：" + e.getMessage() );
        }

    }

    /**
     * 设置事务是否自动提交、隔离级别
     */
    public static void setTransaction() {
        if( isValidate() ) {
            try {
                conn.setAutoCommit(JDBCHelper.autocommit);
            } catch (SQLException e) {
                System.out.println("设置是否自动提交事务失败 " + e.getMessage() );
            }
            try {
                conn.setTransactionIsolation(JDBCHelper.isolation);
            } catch (SQLException e) {
                System.out.println("设置隔离级别失败 ：" + e.getMessage() );
            }
        }
    }

    /**
     * 自定义设置事务是否自动提交、隔离级别
     */
    public static void setTransaction(boolean autoCommit , int isolation ) {
        if( isValidate() ) {
            try {
                conn.setAutoCommit(autoCommit);
            } catch (SQLException e) {
                System.out.println("设置是否自动提交事务失败 " + e.getMessage() );
            }
            try {
                conn.setTransactionIsolation(isolation);
            } catch (SQLException e) {
                System.out.println("设置隔离级别失败 ：" + e.getMessage() );
            }
        }
    }

    /**
     * 创建 Statement 对象
     * @return Statement 对象
     * @throws SQLException
     */
    public static Statement state(){
        Statement st = null ;
        if( !isValidate() ) { // 表示连接是无效的，那么就建立连接
            connect() ;
        }
        try {
            st = conn.createStatement() ;
            System.out.println("statement对象创建成功-state");
            return st ;
        } catch (SQLException e) {
            System.out.println("创建Statement对象失败 ： " + e.getMessage() );
        }
        return  st;

    }

    /**
     * 创建 PreparedStatement 对象
     * @param SQL 表示要预编译的SQL语句
     * @param autoGeneratedKeys 是否要获取自动生成的键
     * 			true表示 获取 ，false表示不获取
     * @return PreparedStatement 对象
     * @throws SQLException
     */
    public static PreparedStatement prestate( String SQL ,  boolean autoGeneratedKeys ) {
        PreparedStatement ps = null ;
        if( !isValidate() ) { // 表示连接是无效的，那么就建立连接
            connect() ;
        }
        try {
            if( autoGeneratedKeys ) {
                ps = conn.prepareStatement(SQL , Statement.RETURN_GENERATED_KEYS ) ;
            }else {
                ps = conn.prepareStatement(SQL) ;
            }
            System.out.println("PreparedStatement对象创建成功-prestate");
            return ps ;
        } catch (SQLException e) {
            System.out.println("创建PreparedStatement失败 : " + e.getMessage());
        }
        return ps ;

    }

    /**
     * 执行DDL、DML
     */
    public static boolean executeUpdate(String SQL , Object...params ) {
        boolean result  = false ;
        if( Objects.isNull( SQL ) || SQL.trim().isEmpty() ) {
            throw new RuntimeException("你传入的SQL是空串") ;
        }else if( SQL.trim().toLowerCase().startsWith("select")){
            throw new RuntimeException("查询语句请使用executeQuery(String SQL , 							Object...params") ;
        }
        SQL = SQL.trim().toLowerCase()  ;
        String prefix = SQL.substring(0, SQL.indexOf(" " )) ;
        String opreation = "" ;
        switch (prefix) {
            case "insert":	 opreation="插入数据" ; 	break;
            case "update":	 opreation="更新数据" ;  	break;
            case "delete":	  opreation="删除数据" ; 	break;
            case "truncate":	 opreation="截断表" ;  	break;
            case "create":	 opreation="创建表" ;  	break;
            case "drop":	 opreation="删除表" ; 	break;
            case "alter":	  opreation="修改表" ; 	break;
            case "rename":	  opreation="重命名表" ; 	break;
        }
        Connection c  = null ;
        if( params.length > 0 ) {
            PreparedStatement ps = prestate(SQL, false) ;
            try {
                c = ps.getConnection() ;
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            try {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject( i + 1, params[i]);
                }
                ps.executeUpdate() ;
                /**是否要自动提交 */
                commit(c) ;
                result = true ;
            } catch (SQLException e) {
                System.out.println(opreation +"操作失败," + e.getMessage());
                /**
                 * 应该考虑回滚
                 */
                rollback(c) ;
            }
        }else {
            Statement st = state() ;
            try {
                c = st.getConnection() ;
            } catch (SQLException e2) {
                // TODO Auto-generated catch block
                e2.printStackTrace();
            }
            try {
                st.executeUpdate(SQL) ;
                /***
                 * 考虑事务的提交
                 */
                commit(c) ;
                result = true ;
            } catch (SQLException e) {
                System.out.println(opreation +"操作失败" + e.getMessage());
                /**
                 * 应该考虑回滚
                 */
                rollback(c) ;
            }
        }
        return result;
    }


    public static ResultSet executeQuery(String SQL , Object...params){
        ResultSet rs = null;
        if(Objects.isNull(SQL)) {
            System.out.println("sql为空串");
            return rs ;
        }
        Connection c = null ;
        if( params.length > 0 ) {
            PreparedStatement ps = prestate(SQL, false) ;
            try {
                c = ps.getConnection() ;
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            try {

                for (int i = 0; i < params.length; i++) {
                    ps.setObject( i + 1, params[i]);
                }
                rs = ps.executeQuery() ;
                /**是否要自动提交 */
                commit(c) ;
            } catch (SQLException e) {
                System.out.println("查询操作失败," + e.getMessage());
                /**
                 * 应该考虑回滚
                 */
                rollback(c) ;
            }
        }else {
            Statement st = state() ;

            try {
                c = st.getConnection() ;
                rs = st.executeQuery(SQL) ;
                /***
                 * 考虑事务的提交
                 */
                commit( c ) ;
            } catch (SQLException e) {
                System.out.println("查询操作失败" + e.getMessage());
                /**
                 * 应该考虑回滚
                 */
                rollback( c ) ;
            }
        }
        return rs;
    }

    /**
     * 处理事务
     */
    private static void commit(Connection c){
        if(Objects.nonNull(c) && !autocommit)
            try {
                c.commit();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
    private static void rollback(Connection c){
        if(Objects.nonNull(c) && !autocommit)
            try {
                c.rollback();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }

    /**
     * 处理结果集,查看结果集
     */
    public static void dealResults(ResultSet rs) {
        if(Objects.nonNull(rs)) {
            ResultSetMetaData rsmd = null ;
            try {
                rsmd = rs.getMetaData() ;
                int count = rsmd.getColumnCount() ;
                for (int i = 0; i < count; i++) {
                    //输出列名
                    System.out.print(rsmd.getColumnName(i+1)+"\t");
                }
                System.out.println();
                while(rs.next()) {
                    for (int i = 0; i < count; i++) {
                        //输出一行内每一列的数据
                        System.out.print(rs.getObject(i+1)+"\t");
                    }
                    System.out.println();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    }
    /**
     * 关闭资源
     */
    public static void release(Object o){
        if(Objects.nonNull(o)){
            if(o instanceof Statement) {
                Statement st = (Statement) o ;
                try {
                    st.close();
                } catch (SQLException e) {
                    System.out.println("关闭Statement失败" + e.getMessage());
                }
            }
            if(o instanceof ResultSet) {
                ResultSet rs = (ResultSet) o ;
                try {
                    rs.close();
                } catch (SQLException e) {
                    System.out.println("关闭结果集失败" + e.getMessage());
                }
            }
            if(o instanceof Connection) {
                Connection con = (Connection) o ;
                try {
                    con.close();
                } catch (SQLException e) {
                    System.out.println("关闭连接失败" + e.getMessage());
                }
            }
        }
    }
}

